import knex, { Knex } from 'knex';
import path from 'path';
import fs from 'fs';
import { Logger } from '@/utils/logger';

export class DatabaseConnection {
  private static instance: Knex | null = null;
  private static logger = new Logger('Database');

  public static async initialize(): Promise<void> {
    try {
      // 确保数据目录存在
      const dataDir = path.dirname(process.env.DB_PATH || './data/health_assistant.db');
      if (!fs.existsSync(dataDir)) {
        fs.mkdirSync(dataDir, { recursive: true });
      }

      // 创建数据库连接
      this.instance = knex({
        client: 'sqlite3',
        connection: {
          filename: process.env.DB_PATH || './data/health_assistant.db'
        },
        useNullAsDefault: true,
        migrations: {
          directory: path.join(__dirname, 'migrations'),
          extension: 'ts'
        },
        seeds: {
          directory: path.join(__dirname, 'seeds'),
          extension: 'ts'
        },
        pool: {
          min: 1,
          max: 10,
          acquireTimeoutMillis: 60000,
          createTimeoutMillis: 30000,
          destroyTimeoutMillis: 5000,
          idleTimeoutMillis: 30000,
          reapIntervalMillis: 1000,
          createRetryIntervalMillis: 200
        }
      });

      // 启用外键约束
      await this.instance.raw('PRAGMA foreign_keys = ON');
      
      // 运行迁移
      await this.runMigrations();
      
      this.logger.info('Database connection initialized successfully');
    } catch (error) {
      this.logger.error('Failed to initialize database:', error);
      throw error;
    }
  }

  public static getConnection(): Knex {
    if (!this.instance) {
      throw new Error('Database not initialized. Call initialize() first.');
    }
    return this.instance;
  }

  public static async close(): Promise<void> {
    if (this.instance) {
      await this.instance.destroy();
      this.instance = null;
      this.logger.info('Database connection closed');
    }
  }

  private static async runMigrations(): Promise<void> {
    try {
      const [batchNo, log] = await this.instance!.migrate.latest();
      
      if (log.length === 0) {
        this.logger.info('Database is already up to date');
      } else {
        this.logger.info(`Batch ${batchNo} migrations completed:`);
        log.forEach(migration => {
          this.logger.info(`- ${migration}`);
        });
      }
    } catch (error) {
      this.logger.error('Migration failed:', error);
      throw error;
    }
  }

  public static async rollbackMigration(): Promise<void> {
    try {
      const [batchNo, log] = await this.instance!.migrate.rollback();
      
      if (log.length === 0) {
        this.logger.info('No migrations to rollback');
      } else {
        this.logger.info(`Batch ${batchNo} migrations rolled back:`);
        log.forEach(migration => {
          this.logger.info(`- ${migration}`);
        });
      }
    } catch (error) {
      this.logger.error('Migration rollback failed:', error);
      throw error;
    }
  }

  public static async seedDatabase(): Promise<void> {
    try {
      await this.instance!.seed.run();
      this.logger.info('Database seeding completed');
    } catch (error) {
      this.logger.error('Database seeding failed:', error);
      throw error;
    }
  }

  public static async healthCheck(): Promise<boolean> {
    try {
      await this.instance!.raw('SELECT 1');
      return true;
    } catch (error) {
      this.logger.error('Database health check failed:', error);
      return false;
    }
  }
}